# -*- coding: utf-8 -*-
# @Author : y
# @Date   : 2022/4/22
# @File   : 
# @E-mail : zhaochen@bbdservice.com

'''eof
name:企业行政案件司法校验
code:R87_EG_EntAdministrativeCaseCheck_XD_SC
tableName:
columnName:
groups:个人及企业司法校验
dependencies:EG_CASE_EDMP,TX_SC_DSJ
type:常用指标
datasourceType:在线指标
description:
eof'''

import re
import pandas as pd
import json
import datetime
from collections import OrderedDict
from dateutil.relativedelta import relativedelta

def getTable1(object, caseType, tableName):
    """
    object: 'person','enterprise'
    caseType:'civil','administrative','preservation','criminal','bankrupt','implement'
    tableName:'cases','count'
    """
    null_type_list = ['', ' ', None, 'null', 'Null', 'NULL', '/', '-', '--']
    if object == 'enterprise':
        try:
            data1 = EG_CASE_EDMP.get('data').get('FR001')
            if data1 in null_type_list:
                return u'缺失值'
            elif data1 == []:
                pass
            else:
                try:
                    data1= data1[0].get(caseType)
                    if data1 == {} or data1 == '':
                        pass
                    else:
                        data1 = data1.get(tableName)
                        if data1 in null_type_list:
                            return u'缺失值'
                        else:
                            pass
                except:
                    return u'缺失值'
            data2 = EG_CASE_EDMP.get('data').get('R001')
            if data2 in null_type_list:
                return u'缺失值'
            elif data2 == []:
                pass
            else:
                try:
                    data2 = data2[0].get(caseType)
                    if data2 == {} or data2 == '':
                        pass
                    else:
                        data2 = data2.get(tableName)
                        if data2 in null_type_list:
                            return u'缺失值'
                        else:
                            pass
                except:
                    return u'缺失值'
        except:
            return u'缺失值'              
    else:
        try:
            data1 = EG_CASE_EDMP.get('data').get('FR002')
            if data1 in null_type_list:
                return u'缺失值'
            elif data1 == []:
                pass
            else:
                try:
                    data1= data1[0].get(caseType)
                    if data1 == {} or data1 == '':
                        pass
                    else:
                        data1 = data1.get(tableName)
                        if data1 in null_type_list:
                            return u'缺失值'
                        else:
                            pass
                except:
                    return u'缺失值'
            data2 = EG_CASE_EDMP.get('data').get('R002')
            if data2 in null_type_list:
                return u'缺失值'
            elif data2 == []:
                pass
            else:
                try:
                    data2 = data2[0].get(caseType)
                    if data2 == {} or data2 == '':
                        pass
                    else:
                        data2 = data2.get(tableName)
                        if data2 in null_type_list:
                            return u'缺失值'
                        else:
                            pass
                except:
                    return u'缺失值'
        except:
            return u'缺失值'

    if tableName == 'cases':
        if (data1 == [] or data1 == {} or data1 == '') and (data2 == [] or data2 == {} or data2 == ''):
            return 'L'
        else:
            data = []
            for i in data1:
                if i not in null_type_list and i != {} and i != '' and i != []:
                    data.append(i)
                else:
                    pass
            for j in data2:
                if j not in null_type_list and j != {} and j != '' and j != []:
                    data.append(j)
                else:
                    pass
    else:
        data = []
        for i in [data1, data2]:
            if i not in null_type_list and i != {} and i != '':
                data.append(i)
            else:
                return u'缺失值'       
    return data

def convertDataType(data_value, data_type):
    """
    数据格式转换
    :param data_value:
    :param data_type: float/int/str/date_time
    :return:
    """
    data_value = str(data_value)
    return_data = None
    # float
    if data_type == 'float':
        try:
            return_data = float(data_value) if len(data_value) >= 0 else None
        except:
            return_data = None
    # int
    elif data_type == 'int':
        try:
            return_data = int(data_value) if len(data_value) >= 0 else None
        except:
            return_data = None
    # str
    elif data_type == 'str':
        try:
            return_data = str(data_value) if len(data_value) >= 0 else None
        except:
            return_data = None
    # date_time
    elif data_type == 'date_time':
        r = re.compile(r'\D+')
        try:
            return_data = datetime.datetime.strptime(data_value, '%Y.%m.%d').strftime('%Y-%m-%d')
        except:
            try:
                return_data = datetime.datetime.strptime(data_value, '%Y-%m-%d').strftime('%Y-%m-%d')
            except:
                try:
                    return_data = datetime.datetime.strptime(data_value, '%Y/%m/%d').strftime('%Y-%m-%d')
                except:
                    try:
                        data_value = r.sub('', data_value)
                        return_data = datetime.datetime.strptime(data_value, '%Y%m%d').strftime('%Y-%m-%d')
                    except:
                        return_data = None

    return return_data

def get_relative_date():
    relative_date = ratingRequest.get('relativeDate')
    return convertDataType(relative_date,'date_time')  

def formatDate(time):
    
    import re
    import datetime
    try:
        r = re.compile(r'\D')
        tmp = r.sub('', time)
        if tmp.isdigit():
            date = datetime.datetime.strptime(tmp, '%Y%m%d').strftime('%Y%m%d')
            return date
        else:
            return None
    except:
        return None
    
def isfloat(x):
    '''
    判断x为float类型
    :param x:
    :return:
    '''
    try:
        if isinstance(float(x), float):
            if str(x) in ['inf', 'infinity', 'INF', 'INFINITY', 'True', 'NAN', 'nan','False', '-inf', '-INF', '-INFINITY', '-infinity', 'NaN', 'Nan']:
                return False
            else:
                return True
        else:
            return False
    except:
        return False
    
def getTimeDiff(CaseTime):
    """
    1.获取结案时间,若结案时间(d_jarq)为空,则获取立案时间(d_larq),若立案时间(d_larq)也为空, 则返回None 
    2.获取当前时间now
    3.计算以日为单位的二者时间差,然后除以365, 结果保留一位小数, 可得以年为单位的时间间隔"
    """    
    case_time = pd.to_datetime(formatDate(CaseTime))
    now = datetime.datetime.now()
    diff_year = round((now - case_time).days/365.0,9)
            
    return diff_year

def getMoneyLevel(CaseMoney):
    """
    """
    if  CaseMoney in null_type_list or float(CaseMoney) == 0:
        MoneyLevel = '0'
    elif float(CaseMoney) <= 10000:
        MoneyLevel = '1'
    elif 10000 < float(CaseMoney) <= 20000:
        MoneyLevel = '2'
    elif 20000 < float(CaseMoney) <= 30000:
        MoneyLevel = '3'
    elif 30000 < float(CaseMoney) <= 40000:
        MoneyLevel = '4' 
    elif 40000 < float(CaseMoney) <= 50000:
        MoneyLevel = '5' 
    elif 50000 < float(CaseMoney) <= 60000:
        MoneyLevel = '6' 
    elif 60000 < float(CaseMoney) <= 70000:
        MoneyLevel = '7'                                   
    elif 70000 < float(CaseMoney) <= 80000:
        MoneyLevel = '8'           
    elif 80000 < float(CaseMoney) <= 90000:
        MoneyLevel = '9'       
    elif 90000 < float(CaseMoney) <= 100000:
        MoneyLevel = '10'
    elif 100000 < float(CaseMoney) <= 200000:
        MoneyLevel = '11' 
    elif 200000 < float(CaseMoney) <= 300000:
        MoneyLevel = '12'
    elif 300000 < float(CaseMoney) <= 400000:
        MoneyLevel = '13'
    elif 400000 < float(CaseMoney) <= 500000:
        MoneyLevel = '14'  
    elif 500000 < float(CaseMoney) <= 600000:
        MoneyLevel = '15'
    elif 600000 < float(CaseMoney) <= 700000:
        MoneyLevel = '16'      
    elif 700000 < float(CaseMoney) <= 800000:
        MoneyLevel = '17'  
    elif 800000 < float(CaseMoney) <= 900000:
        MoneyLevel = '18'
    elif 900000 < float(CaseMoney) <= 1000000:
        MoneyLevel = '19'
    elif 1000000 < float(CaseMoney) <= 1500000:
        MoneyLevel = '20'
    else:
        MoneyLevel = '21'
                
    return MoneyLevel


#获取最高等级
def getMaxLevel(LevelList):
    level_seq_list = ['H','M6','M5','M4','M3','M2','M1','L']
    for i in level_seq_list:
        if i in LevelList:
            max_level = i 
            break
        else:
            pass
    return max_level

null_type_list = ['', ' ', None, 'null', 'Null', 'NULL', '/', '-', '--']
last_month_list = [(datetime.datetime.now() - pd.tseries.offsets.DateOffset(months=i)).strftime('%Y%m%d')[:6] for i in range(1, 30)]

def getTable(table_Name):
    '''
    获取表
    :param x:
    :return:
    '''
    try:
        ic_data = TX_SC_DSJ['data'].get(table_Name)
        if ic_data in null_type_list:
            return None
        else:
            return ic_data if isinstance(ic_data, list) and len(ic_data) > 0 else [{}]
    except:
        return None

def TaxerType():
    try:
        syptSwdjxx = getTable('syptSwdjxx')
        if syptSwdjxx == [{}] or syptSwdjxx is None:
            return None
        else:
            NSRZG_DM = syptSwdjxx[0].get('NSRZG_DM')
            if NSRZG_DM in null_type_list:
                return None
            elif NSRZG_DM not in null_type_list and NSRZG_DM in ['201', '202', '203', '204', '205']:
                return NSRZG_DM
            else:
                return u'其他'
    except:
        return None

#一般纳税人
def ybnsr():
    try:
        syptZzsybnsr = getTable('syptZzsybnsr') 
        if syptZzsybnsr is None or syptZzsybnsr == [{}]:
            return 'None'
        else:
            # 可能存在统一年月 存在多次数据需要 用dict去重
            Reven_list = OrderedDict()
            for k in last_month_list:
                Reven_list.update({k:'None'})
            flag = []
            for i in syptZzsybnsr:
                QSRQ = i.get('skssqq')
                QZRQ = i.get('skssqz')
                if QSRQ in null_type_list or QZRQ in null_type_list or formatDate(QSRQ) == None or formatDate(QZRQ) == None:
                    flag.append(None)
                    continue
                else:
                    # QSNY = str(QSRQ[:4])+str(QSRQ[5:7])
                    # QZNY = str(QZRQ[:4])+str(QZRQ[5:7])
                    Mons = (int(QZRQ[:4])-int(QSRQ[:4]))*12 + (int(QZRQ[5:7])-int(QSRQ[5:7]))
                    noneCont = 0
                    if(Mons==0):
                        ND = QSRQ[:4]
                        YF = QSRQ[5:7]
                        # noneCont = 0
                        if ND not in null_type_list and YF in ['01', '02', '03', '04', '05', '06', '07','08', '09', '10', '11', '12']:
                            # 按适用税率计税销售额(ASYSLJSDNSJCYBJSE)
                            if isfloat(i.get('ASYSLJSDNSJCYBJSE')):
                                a1 = float(i.get('ASYSLJSDNSJCYBJSE'))
                            else:
                                a1 = None
                                noneCont += 1
                            # “按简易办法计税销售额(AJYZSBFZSHWXSE)”
                            if isfloat(i.get('AJYZSBFZSHWXSE')):
                                a2 = float(i.get('AJYZSBFZSHWXSE'))
                            else:
                                a2 = None
                                noneCont += 1
                            # 免、抵、退办法出口销售额(MDTBFCKHWXSE)
                            if isfloat(i.get('MDTBFCKHWXSE')):
                                a3 = float(i.get('MDTBFCKHWXSE'))
                            else:
                                a3 = None
                                noneCont += 1
                            # 免税销售额(MSHWJLWXSE)
                            if isfloat(i.get('MSHWJLWXSE')):
                                a4 = float(i.get('MSHWJLWXSE'))
                            else:
                                a4 = None
                                noneCont += 1
                            if noneCont == 4 :
                                pass
                            elif a1==a2==a1==a3==a4==None:
                                pass
                            else:
                                if a1 == None:
                                    a1=0
                                if a2 == None:
                                    a2=0
                                if a3 == None:
                                    a3=0
                                if a4 == None:
                                    a4=0
                                # 存入每个月的收入
                                Reven_Value = a1+a2+a3+a4
                                key = str(ND) + str(YF)
                                if Reven_list.get(key) == 'None':
                                    Reven_list.update({key: Reven_Value})
                        else:
                            flag.append(None)
                            continue
                    elif(Mons>0):
                            # 按适用税率计税销售额(ASYSLJSDNSJCYBJSE)
                            if isfloat(i.get('ASYSLJSDNSJCYBJSE')):
                                a1 = float(i.get('ASYSLJSDNSJCYBJSE'))
                            else:
                                a1 = None
                                noneCont += 1
                            # “按简易办法计税销售额(AJYZSBFZSHWXSE)”
                            if isfloat(i.get('AJYZSBFZSHWXSE')):
                                a2 = float(i.get('AJYZSBFZSHWXSE'))
                            else:
                                a2 = None
                                noneCont += 1
                            # 免、抵、退办法出口销售额(MDTBFCKHWXSE)
                            if isfloat(i.get('MDTBFCKHWXSE')):
                                a3 = float(i.get('MDTBFCKHWXSE'))
                            else:
                                a3 = None
                                noneCont += 1
                            # 免税销售额(MSHWJLWXSE)
                            if isfloat(i.get('MSHWJLWXSE')):
                                a4 = float(i.get('MSHWJLWXSE'))
                            else:
                                a4 = None
                                noneCont += 1
                            if noneCont == 4:
                                pass
                            elif a1==a2==a1==a3==a4==None:
                                pass
                            else:
                                # 存入收入
                                if a1 == None:
                                    a1=0
                                if a2 == None:
                                    a2=0
                                if a3 == None:
                                    a3=0
                                if a4 == None:
                                    a4=0
                                Reven_Value = a1+a2+a3+a4
                                Reven_Ave = round(float(Reven_Value)/(Mons+1),4)
                                for  j in range(Mons+1):
                                    NDYF = datetime.datetime.strptime(QSRQ,'%Y-%m-%d') + relativedelta(months=j)
                                    ND = str(NDYF)[:4]
                                    YF = str(NDYF)[5:7]
                                    # noneCont = 0
                                    if ND not in null_type_list and YF in ['01', '02', '03', '04', '05', '06', '07','08', '09', '10', '11', '12']:
                                        key = str(ND) + str(YF)
                                        if Reven_list.get(key) == 'None':
                                            Reven_list.update({key: Reven_Ave})
                                        else:
                                            pass
                                    else:
                                        flag.append(None)
                                        continue
                    else:
                        flag.append(None)
                        continue
            if len(flag) == len(syptZzsybnsr):
                Reven_list = 'None'
            return Reven_list
    except:
        return 'None'

#小规模纳税人
def xgm():
    try:
        syptZzsxgm = getTable('syptZzsxgm')
        if syptZzsxgm is None or syptZzsxgm == [{}]:
            return 'None'
        else:
            Reven_list = OrderedDict()
            for k in last_month_list:
                Reven_list.update({k:'None'})
            flag = []
            for i in syptZzsxgm:
                QSRQ = i.get('skssqq')
                QZRQ = i.get('skssqz')
                if QSRQ in null_type_list or QZRQ in null_type_list or formatDate(QSRQ) == None or formatDate(QZRQ) == None:
                    flag.append(None)
                    continue
                else:
                    # QSNY = str(QSRQ[:4])+str(QSRQ[5:7])
                    # QZNY = str(QZRQ[:4])+str(QZRQ[5:7])
                    Mons = (int(QZRQ[:4])-int(QSRQ[:4]))*12 + (int(QZRQ[5:7])-int(QSRQ[5:7]))
                    noneCount = 0
                    if(Mons==0):
                        ND = QSRQ[:4]
                        YF = QSRQ[5:7]
                        # noneCount = 0
                        if ND not in null_type_list and YF in ['01', '02', '03', '04', '05', '06', '07','08', '09', '10', '11', '12']:
                            #NDYF1=datetime.datetime.strptime(NDYF, '%Y-%m')
                            # “应征增值税货物及劳务不含税销售额(YZZZSHWJLWBHSXSEBNBQ)”
                            if isfloat(i.get('YZZZSHWJLWBHSXSEBNBQ')):
                                a1 = float(i.get('YZZZSHWJLWBHSXSEBNBQ'))
                            else:
                                a1 = None
                                noneCount += 1
                            # “销售、出租不动产不含税销售额(XSCZBDCBHSXSE)”
                            if isfloat(i.get('XSCZBDCBHSXSE')):
                                a2 = float(i.get('XSCZBDCBHSXSE'))
                            else:
                                a2 = None
                                noneCount += 1
                            # “销售使用过的应税固定资产不含税销售额(XSSYGDYSGDZCBHSXSEBNBQ)”
                            if isfloat(i.get('XSSYGDYSGDZCBHSXSEBNBQ')):
                                a3 = float(i.get('XSSYGDYSGDZCBHSXSEBNBQ'))
                            else:
                                a3 = None
                                noneCount += 1
                            # “免税货物及劳务销售额(MSHWJLWXSEBNBQ)”
                            if isfloat(i.get('MSHWJLWXSEBNBQ')):
                                a4 = float(i.get('MSHWJLWXSEBNBQ'))
                            else:
                                a4 = None
                                noneCount += 1
                            #出口免税货物销售额(CKMSHWXSEBNBQ)
                            if isfloat(i.get('CKMSHWXSEBNBQ')):
                                a5 = float(i.get('CKMSHWXSEBNBQ'))
                            else:
                                a5 = None
                                noneCount += 1
                            # 字段都有问题
                            if noneCount == 5:
                                pass
                            elif a1==a2==a1==a3==a4==a5==None:
                                pass
                            else:
                                # 存入每个月的收入
                                if a1 == None:
                                    a1=0
                                if a2 == None:
                                    a2=0
                                if a3 == None:
                                    a3=0
                                if a4 == None:
                                    a4=0
                                if a5 == None:
                                    a5=0
                                Reven_Value = a1+a2+a3+a4+a5
                                key = str(ND) + str(YF)
                                if Reven_list.get(key) == 'None':
                                    Reven_list.update({key: Reven_Value})
                        else:
                            pass
                    elif(Mons>0):
                        # 按适用税率计税销售额(ASYSLJSDNSJCYBJSE)
                        if isfloat(i.get('YZZZSHWJLWBHSXSEBNBQ')):
                            a1 = float(i.get('YZZZSHWJLWBHSXSEBNBQ'))
                        else:
                            a1 = None
                            noneCount += 1
                        # “销售、出租不动产不含税销售额(XSCZBDCBHSXSE)”
                        if isfloat(i.get('XSCZBDCBHSXSE')):
                            a2 = float(i.get('XSCZBDCBHSXSE'))
                        else:
                            a2 = None
                            noneCount += 1
                        # “销售使用过的应税固定资产不含税销售额(XSSYGDYSGDZCBHSXSEBNBQ)”
                        if isfloat(i.get('XSSYGDYSGDZCBHSXSEBNBQ')):
                            a3 = float(i.get('XSSYGDYSGDZCBHSXSEBNBQ'))
                        else:
                            a3 = None
                            noneCount += 1
                        # “免税货物及劳务销售额(MSHWJLWXSEBNBQ)”
                        if isfloat(i.get('MSHWJLWXSEBNBQ')):
                            a4 = float(i.get('MSHWJLWXSEBNBQ'))
                        else:
                            a4 = None
                            noneCount += 1
                        #出口免税货物销售额(CKMSHWXSEBNBQ)
                        if isfloat(i.get('CKMSHWXSEBNBQ')):
                            a5 = float(i.get('CKMSHWXSEBNBQ'))
                        else:
                            a5 = None
                            noneCount += 1
                        # 字段都有问题
                        if noneCount == 5:
                            pass
                        elif a1==a2==a1==a3==a4==a5==None:
                            pass
                        else:
                            # 存入收入
                            if a1 == None:
                                a1=0
                            if a2 == None:
                                a2=0
                            if a3 == None:
                                a3=0
                            if a4 == None:
                                a4=0
                            if a5 == None:
                                a5=0
                            Reven_Value = a1+a2+a3+a4+a5
                            Reven_Ave = round(float(Reven_Value)/(Mons+1),4)
                            for  j in range(Mons+1):
                                NDYF = datetime.datetime.strptime(QSRQ,'%Y-%m-%d') + relativedelta(months=j)
                                ND = str(NDYF)[:4]
                                YF = str(NDYF)[5:7]
                                # noneCount = 0
                                if ND not in null_type_list and YF in ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']:
                                    key = str(ND) + str(YF)
                                    if Reven_list.get(key) == 'None':
                                        Reven_list.update({key: Reven_Ave})
                                    else:
                                        pass
                                else:
                                    flag.append(None)
                                    continue
                    else:
                        flag.append(None)
                        pass
            if len(flag) == len(syptZzsxgm):
                Reven_list = 'None'
            return Reven_list
    except:
        return 'None'

def getMoneyRate(Money):
    try:
        syptZzsxgm = getTable('syptZzsxgm')
        syptZzsybnsr = getTable('syptZzsybnsr')
        if syptZzsxgm in [None, [{}]] and syptZzsybnsr in [None, [{}]]:
            return u'缺失值'
        elif syptZzsxgm not in [None, [{}]] and syptZzsybnsr in [None, [{}]]:
            Reven_list = xgm()
            if Reven_list  == 'None':
                return u'缺失值'
            else:
                Reven_list_Value1 = Reven_list.values()
                Reven_list_sub = OrderedDict()
                for k, v in Reven_list.items():
                    if v == 'None':
                        Reven_list_sub.update({k:0})
                    else:
                        Reven_list_sub.update({k:v})
                Reven_list_Value2 = Reven_list_sub.values()
                if  Reven_list_Value1[0] != 'None':
                    Reven1Y_Sum = sum(Reven_list_Value2[0:12])
                elif Reven_list_Value1[0] == 'None' and Reven_list_Value1[1] != 'None':
                    Reven1Y_Sum = sum(Reven_list_Value2[1:13])
                elif Reven_list_Value1[0] == 'None' and Reven_list_Value1[1] == 'None' and Reven_list_Value1[2] != 'None':
                    Reven1Y_Sum = sum(Reven_list_Value2[2:14])
                else:
                    Reven1Y_Sum = sum(Reven_list_Value2[3:15])
        elif syptZzsxgm in [None, [{}]] and syptZzsybnsr not in [None, [{}]]:
            Reven_list = ybnsr()
            if Reven_list  == 'None':
                return u'缺失值'
            else:
                Reven_list_Value1 = Reven_list.values()
                Reven_list_sub = OrderedDict()
                for k, v in Reven_list.items():
                    if v == 'None':
                        Reven_list_sub.update({k:0})
                    else:
                        Reven_list_sub.update({k:v})
                Reven_list_Value2 = Reven_list_sub.values()
                if  Reven_list_Value1[0] != 'None':
                    Reven1Y_Sum = sum(Reven_list_Value2[0:12])
                else:
                    Reven1Y_Sum = sum(Reven_list_Value2[1:13])
        else:
            #合并 ybnsr 和 xgm 计算
            Reven_list1 = xgm()
            Reven_list2 = ybnsr()
            Reven_list = OrderedDict()
            # 二者同时为空 才返回空
            if Reven_list1 == 'None' and Reven_list2 == 'None':
                Reven_list = None
            elif  Reven_list1 != 'None' and Reven_list2 == 'None':
                Reven_list = Reven_list1
            elif  Reven_list1 == 'None' and Reven_list2 != 'None':
                Reven_list = Reven_list2
            else:
                #纳税类型
                taxerType = TaxerType()
                # 一般纳税人,优先取一般纳税人
                if taxerType in ['201', '202', '203']:
                    for k, v in Reven_list2.items():
                        if v == 'None':
                            tmp = Reven_list1.get(k)
                            if tmp != 'None':
                                Reven_list.update({k:tmp})
                            else:
                                Reven_list.update({k:'None'})
                        else:
                            Reven_list.update({k:v})
                # 小规模,优先取小规模
                elif taxerType in ['204', '205']:
                    for k, v in Reven_list1.items():
                        if v == 'None':
                            tmp = Reven_list2.get(k)
                            if tmp != 'None':
                                Reven_list.update({k:tmp})
                            else:
                                Reven_list.update({k:'None'})
                        else:
                            Reven_list.update({k:v})
                #其他情况
                else:
                    # 取出小规模纳税人表 和 一般纳税人表中 缴税年度和月份进行比较
                    ybnsr_list = []
                    xgm_list = []
                    for i in syptZzsxgm:
                        QZRQ = i.get('skssqz')
                        if QZRQ in null_type_list or formatDate(QZRQ) == None:
                            pass
                        else:
                            NDYF = datetime.datetime.strptime(QZRQ,'%Y-%m-%d')
                            ND = str(NDYF)[:4]
                            YF = str(NDYF)[5:7]
                            # ND = i.get('ND')
                            # YF = i.get('YF')
                            try:
                                d = float(ND + YF)
                            except:
                                continue
                            if isfloat(d):
                                xgm_list.append(float(d))
                            else:
                                pass
                    for i in syptZzsybnsr:
                        QZRQ = i.get('skssqz')
                        if QZRQ in null_type_list or formatDate(QZRQ) == None:
                            pass
                        else:
                            NDYF = datetime.datetime.strptime(QZRQ,'%Y-%m-%d')
                            ND = str(NDYF)[:4]
                            YF = str(NDYF)[5:7]
                            # ND = i.get('ND')
                            # YF = i.get('YF')
                            try:
                                d = float(ND + YF)
                            except:
                                continue
                            if isfloat(d):
                                ybnsr_list.append(float(d))
                            else:
                                pass
                    if ybnsr_list == [] and xgm_list == []:
                        Reven_list = None
                    elif ybnsr_list != [] and xgm_list != []:
                        if max(ybnsr_list) >= max(xgm_list):
                            for k, v in Reven_list2.items():
                                if v == 'None':
                                    tmp = Reven_list1.get(k)
                                    if tmp != 'None':
                                        Reven_list.update({k:tmp})
                                    else:
                                        Reven_list.update({k:'None'})
                                else:
                                    Reven_list.update({k:v})
                        else:
                            for k, v in Reven_list1.items():
                                if v == 'None':
                                    tmp = Reven_list2.get(k)
                                    if tmp != 'None':
                                        Reven_list.update({k:tmp})
                                    else:
                                        Reven_list.update({k:'None'})
                                else:
                                    Reven_list.update({k:v})
                    elif ybnsr_list != [] and xgm_list == []:
                        for k, v in Reven_list2.items():
                            if v == 'None':
                                tmp = Reven_list1.get(k)
                                if tmp != 'None':
                                    Reven_list.update({k:tmp})
                                else:
                                    Reven_list.update({k:'None'})
                            else:
                                Reven_list.update({k:v})
                    elif ybnsr_list == [] and xgm_list != []:
                        for k, v in Reven_list1.items():
                            if v == 'None':
                                tmp = Reven_list2.get(k)
                                if tmp != 'None':
                                    Reven_list.update({k:tmp})
                                else:
                                    Reven_list.update({k:'None'})
                            else:
                                Reven_list.update({k:v})
                    else:
                        assert False
            if Reven_list in null_type_list:
                return u'缺失值'
            else:
                Reven_list_Value1 = Reven_list.values()
                Reven_list_sub = OrderedDict()
                for k, v in Reven_list.items():
                    if v == 'None':
                        Reven_list_sub.update({k:0})
                    else:
                        Reven_list_sub.update({k:v})
                Reven_list_Value2 = Reven_list_sub.values()
                #纳税类型
                taxerType = TaxerType()
                # 一般纳税人,优先取一般纳税人
                if taxerType in ['201', '202', '203']:
                    if Reven_list_Value1[0] != 'None':
                        Reven1Y_Sum = sum(Reven_list_Value2[0:12])
                    else:
                        Reven1Y_Sum = sum(Reven_list_Value2[1:13])
                else:
                    if  Reven_list_Value1[0] != 'None':
                        Reven1Y_Sum = sum(Reven_list_Value2[0:12])
                    elif Reven_list_Value1[0] == 'None' and Reven_list_Value1[1] != 'None':
                        Reven1Y_Sum = sum(Reven_list_Value2[1:13])
                    elif Reven_list_Value1[0] == 'None' and Reven_list_Value1[1] == 'None' and Reven_list_Value1[2] != 'None':
                        Reven1Y_Sum = sum(Reven_list_Value2[2:14])
                    else:
                        Reven1Y_Sum = sum(Reven_list_Value2[3:15])
    except:
        return u'缺失值' 
    
    MoneyRate = round(float(Money)/Reven1Y_Sum,8)  
    return MoneyRate        

def getCaseNum(case_ah,case_hxah):
    """
    """
    
    for i in case_ah:
        if i in null_type_list:
            return u'缺失值'
        else:
            pass
        
    CaseNum = 0    
    for j in case_hxah:
        if j == u'暂无' or j == '':
            CaseNum += 1
            
    return CaseNum
        
def getCaseImportance(jaay,laay):
    """
    n_jaay_tree:结案案由详情
    n_laay_tree:立案案由详情
    """
    if jaay in null_type_list and laay in null_type_list:
        return u'缺失值'
    elif jaay not in null_type_list and laay in null_type_list:
        case_ay = jaay
        if u'民间' in case_ay or u'借贷' in case_ay or u'借款' in case_ay or u'离婚' in case_ay:
            case_important = u'重要案件'
        elif u'物业' in case_ay or u'劳动' in case_ay or u'劳务' in case_ay or u'机动车' in case_ay or u'人事' in case_ay:
            case_important = u'不重要案件'
        else:
            case_important = u'一般案件'          
    elif laay not in null_type_list and jaay in null_type_list:
        case_ay = laay
        if u'民间' in case_ay or u'借贷' in case_ay or u'借款' in case_ay or u'离婚' in case_ay:
            case_important = u'重要案件'
        elif u'物业' in case_ay or u'劳动' in case_ay or u'劳务' in case_ay or u'机动车' in case_ay or u'人事' in case_ay:
            case_important = u'不重要案件'
        else:
            case_important = u'一般案件'
    else:
        case_ay = [jaay,laay]
        case_important_list = []
        for i in case_ay:
            if u'民间' in i or u'借贷' in i or u'借款' in i or u'离婚' in i:
                case_important_list.append(u'重要案件')
            elif u'物业' in i or u'劳动' in i or u'劳务' in i or u'机动车' in i or u'人事' in i:
                case_important_list.append(u'不重要案件')
            else:
                case_important_list.append(u'一般案件')
            if u'重要案件' in case_important_list:
                case_important = u'重要案件'
            elif u'一般案件' in case_important_list:
                case_important = u'一般案件'
            else:
                case_important = u'不重要案件'
                    
    return case_important

def AdministrativeCheck(): 
    relativeDate = get_relative_date()
    # 判断 relativeDate 是否符合要求
    if relativeDate is None:
        raise ValueError(u"网贷传输的数据字段relativeDate缺失或不符合要求")   
    
    null_type_list = ['', ' ', None, 'null', 'Null', 'NULL', '/', '-', '--']
    caseList = getTable1('enterprise', 'administrative', 'cases')
    if caseList == 'L':
        return 1
    else:
        pass
    
    CaseLevelList = []
    for i in caseList:
        try:
            ajjzjd = i.get('n_ajjzjd') #案件进展阶段(审理阶段)
            if ajjzjd in null_type_list:
                return u'缺失值'
            else: 
                try:
                    ssdw = i.get('n_ssdw') #诉讼地位
                    if ssdw in null_type_list:
                        return u'缺失值'
                    else:
                        pass
                except:
                    return u'缺失值'
        except:
            return U'缺失值'
        try:
            CaseTime = i.get('d_jarq') #结案日期
            if CaseTime in null_type_list:
                CaseTime = i.get('d_larq') #立案日期
                if CaseTime in null_type_list:
                    if ajjzjd == u'未结案':
                        pass
                    else:
                        return u'缺失值'
                else:
                    pass
            else:
                pass
        except:
            return u'缺失值' 
        if convertDataType(CaseTime,'date_time') > relativeDate:
            if ajjzjd == u'未结案':
                if ssdw in [u'上诉人',u'原告']:
                    CaseLevelList.append('M1')
                else:
                    CaseLevelList.append('M2')
            else:
                try:
                    CaseJafs = i.get('n_jafs') #结案方式
                    if CaseJafs in null_type_list:
                        return u'缺失值'
                    else:
                        pass
                except:
                    return u'缺失值'   
                
                if ssdw in [u'上诉人',u'原告']:
                    if u'撤回' in CaseJafs or u'改判' in CaseJafs or u'调解' in CaseJafs or u'撤销' in CaseJafs or u'撤诉' in CaseJafs: 
                        CaseLevelList.append('L')
                    else:
                        if 0 <= getTimeDiff(CaseTime) <0.5:
                            CaseLevelList.append('M2')
                        elif 0.5 <= getTimeDiff(CaseTime) < 2:
                            CaseLevelList.append('M1')
                        else:
                            CaseLevelList.append('L')
                else:
                    if u'撤回' in CaseJafs or u'撤诉' in CaseJafs or u'调解' in CaseJafs or u'驳回' in  CaseJafs or u'不予立案' in CaseJafs or u'维持' in CaseJafs:
                        if 0 <= getTimeDiff(CaseTime) <0.5:
                            CaseLevelList.append('M2')
                        elif 0.5 <= getTimeDiff(CaseTime) < 2:
                            CaseLevelList.append('M1')
                        else:
                            CaseLevelList.append('L')                
                    else:
                        if 0 <= getTimeDiff(CaseTime) < 1:
                            CaseLevelList.append('M2')
                        elif 1 <= getTimeDiff(CaseTime) < 2:
                            CaseLevelList.append('M1')
                        else:
                            CaseLevelList.append('L')
    if CaseLevelList == []:
        return 1
    else:             
        CaseMaxLevel = getMaxLevel(CaseLevelList)
        
    if CaseMaxLevel == 'L':
        return 1
    elif CaseMaxLevel == 'M1':
        return 1
    elif CaseMaxLevel == 'M2':
        return 1
    elif CaseMaxLevel == 'M3':
        return 1 
    elif CaseMaxLevel == 'M4':
        return 0
    elif CaseMaxLevel == 'M5':
        return 0
    else:
        return 0

result = AdministrativeCheck()